﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GBMS.BLL
{
    public sealed class ReceiveHeader_BLL : IProjectHeader
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable GetDataProjectHeaderByYear(int Year)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Year", Year);

                string sql = "select bgm_rec_header_seq as Sequence"
                                + " , (select coalesce(sum( total_amount ), 0 ) from bgm_receive_control a where a.bgm_rec_header_seq = ah.bgm_rec_header_seq) as total_amount"
                                + " , (select coalesce(sum( approve_amount ), 0 ) from bgm_receive_control a where a.bgm_rec_header_seq = ah.bgm_rec_header_seq) as approve_amount"
                                + ", ah.bgm_rec_header_date as bgm_prj_header_date "
                                + ", ah.bgm_rec_header_name as bgm_prj_header_name "
                                + ", ah.bgm_rec_header_descr as bgm_prj_header_descr ,ah.* "
                                + ", case when ah.record_status = 'A' then 'อนุมัติ' else 'กำลังดำเนินการ' end as status"
                           + " from bgm_receive_header ah"
                           + " where bgm_year = @Year"
                                + " and record_status not like @RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectHeaderBySequence(string seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", seq);
                string sql = "select bgm_rec_header_seq as Sequence"
                           + ", 0 as total_amount"
                           + ", (select coalesce(sum( approve_amount ), 0 ) from bgm_receive_control r where r.bgm_rec_header_seq = "
                           + " rh.bgm_rec_header_seq) as approve_amount"
                           + ", rh.bgm_rec_header_date as bgm_prj_header_date "
                           + ", rh.bgm_rec_header_name as bgm_prj_header_name "
                           + ", rh.bgm_rec_header_descr as bgm_prj_header_descr ,rh.* "
                           + " from bgm_receive_header rh"
                           + " where bgm_rec_header_seq = @Sequence"
                           + " and record_status not like @RecordStatus";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetDataProjectBySequence(string seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Sequence", seq);
                string sql = "select *, bgm_rec_seq as bgm_prj_seq "
                           + "from bgm_receive "
                           + "where bgm_rec_header_seq = @Sequence";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public int InsertProjectHeader(string bgm_rec_header_name, string bgm_rec_header_descr, string bgm_rec_header_date, string bgm_rec_header_result, string bgm_year, string remark, Nullable<int> from_bgm_rec_header_seq, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@bgm_rec_header_name", bgm_rec_header_name);
                dbManager.AddParameters(1, "@bgm_rec_header_descr", bgm_rec_header_descr);
                dbManager.AddParameters(2, "@bgm_rec_header_date", bgm_rec_header_date);
                dbManager.AddParameters(3, "@bgm_rec_header_result", bgm_rec_header_result);
                dbManager.AddParameters(4, "@bgm_year", bgm_year);
                dbManager.AddParameters(5, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(6, "@created_by", created_by);
                dbManager.AddParameters(7, "@created_when", DateTime.Now);

                string strsql = "Insert into bgm_receive_header ("
                                            + "bgm_rec_header_name,"
                                            + "bgm_rec_header_descr,"
                                            + "bgm_rec_header_date,"
                                            + "bgm_rec_header_result,"
                                            + "bgm_year,"
                                            + "record_status,"
                                            + "created_by,"
                                            + "created_when)"
                                            + " values("
                                            + "@bgm_rec_header_name,"
                                            + "@bgm_rec_header_descr,"
                                            + "@bgm_rec_header_date,"
                                            + "@bgm_rec_header_result,"
                                            + "@bgm_year,"
                                            + "@record_status,"
                                            + "@created_by,"
                                            + "@created_when)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                string strsqlLast_insert = "select distinct scope_identity() FROM bgm_receive_header";
                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, strsqlLast_insert);
                dbManager.CommitTransaction();
                return Convert.ToInt32(ds.Tables[0].Rows[0][0]);
            }
            catch
            {
                return -1;
            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void InsertApprovalStrategy(string bgm_app_header_seq, string bgm_strat_list_seq, string bgm_source_seq, decimal total_amount, decimal approve_amount, string created_by)
        { 
        
        }
        public void InsertApprovalSource(string bgm_app_seq, string bgm_source_seq, decimal total_amount, decimal approve_amount, string created_by)
        {

        }

        public void InsertProject(string bgm_rec_header_seq, string bgm_app_header_seq, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@rec", bgm_rec_header_seq);
                dbManager.AddParameters(1, "@app_header", bgm_app_header_seq);
                dbManager.AddParameters(2, "@usr", created_by);
                dbManager.AddParameters(3, "@created_when", DateTime.Now);

                string strsql = "select F_Auto_Create_New_Receive(@rec,@app_header,@usr)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void InsertProjectControl(string bgm_rec_header_seq, string created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@rec", bgm_rec_header_seq);
                dbManager.AddParameters(1, "@usr", created_by);
                dbManager.AddParameters(2, "@created_when", DateTime.Now);

                string strsql = "select F_Auto_Create_New_Receive_Control(@rec,@usr)";    //ไม่ต้อง Loop
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
            }
            finally
            {
                dbManager.Dispose();
            }
        }



        public void UpdateProjectHeader(string Sequence, string bgm_year, string bgm_rec_header_date, string bgm_rec_header_name, string bgm_rec_header_descr, string remark, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);
                dbManager.AddParameters(0, "@sequence", Sequence);
                dbManager.AddParameters(1, "@bgm_year", bgm_year);
                dbManager.AddParameters(2, "@bgm_rec_header_date", bgm_rec_header_date);
                dbManager.AddParameters(3, "@bgm_rec_header_name", bgm_rec_header_name);
                dbManager.AddParameters(4, "@bgm_rec_header_descr", bgm_rec_header_descr);
                dbManager.AddParameters(5, "@remark", remark);
                dbManager.AddParameters(6, "@updated_by", UpdateBy);
                dbManager.AddParameters(7, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_receive_header SET bgm_year=@bgm_year, bgm_rec_header_date=@bgm_rec_header_date, bgm_rec_header_name=@bgm_rec_header_name, bgm_rec_header_descr=@bgm_rec_header_descr, remark=@remark, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_rec_header_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }



        public void DeleteProjectHeader(int Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_receive_header WHERE bgm_rec_header_seq = @Seq";
                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteProject(int Seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@Seq", Seq);
                string strsql = "DELETE FROM bgm_receive WHERE bgm_rec_seq = @Seq;";
                strsql += "DELETE FROM bgm_receive_option WHERE bgm_rec_seq = @Seq;";
                strsql += "DELETE FROM bgm_receive_kpi WHERE bgm_rec_seq = @Seq;";
                strsql += "DELETE FROM bgm_receive_customs_field WHERE bgm_rec_seq = @Seq;";

                strsql += "DELETE FROM bgm_receive_activity_plan_target WHERE bgm_rec_act_plan_seq in (select bgm_rec_act_plan_seq from bgm_receive_activity_plan where bgm_rec_seq = @Seq);";
                strsql += "DELETE FROM bgm_receive_activity_plan WHERE bgm_rec_seq = @Seq;";

                strsql += "DELETE FROM bgm_receive_expense_field WHERE bgm_rec_exp_seq in (select bgm_rec_exp_seq from bgm_receive_expense where bgm_rec_seq = @Seq);";
                strsql += "DELETE FROM bgm_receive_expense_plan WHERE bgm_rec_exp_seq in (select bgm_rec_exp_seq from bgm_receive_expense where bgm_rec_seq = @Seq);";
                strsql += "DELETE FROM bgm_receive_expense WHERE bgm_rec_seq = @Seq;";

                dbManager.Open();
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
                dbManager.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListRequest(string bgm_year, string bgm_strat_list_seq, string job_site_seq, string bgm_rec_header_seq)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@status", SystemInfo._ApproveRecordStatus);

                string sql = " select bgm_app_header_seq as Value"
                            + ", concat( bgm_app_header_name, ' ('"
                                + " , (select sum(total_amount) from bgm_approval a where a.bgm_app_header_seq = m.bgm_app_header_seq ), ' บาท)' ) as TEXT"
                            + " from bgm_approval_header m"
                            + " where bgm_year = " + bgm_year;
                            //+ " and record_status = @status";
                            if (bgm_rec_header_seq != "")
                            {
                                sql += " and bgm_app_header_seq not in (select distinct bgm_app_header_seq from bgm_receive r inner join bgm_approval a on r.bgm_app_seq = a.bgm_app_seq where r.bgm_year = " + bgm_year + " and r.bgm_rec_header_seq = " + bgm_rec_header_seq + " )";
                            }

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable ListApproval(string bgm_rec_header_seq)
        {
            try
            {
                string sql = " select bgm_app_header_seq as Value"
                                + " , concat( bgm_app_header_name, ' ('"
                                + " , (select sum(total_amount) from bgm_approval a where a.bgm_app_header_seq = m.bgm_app_header_seq ), ' บาท)' ) as TEXT"
                            + " from bgm_approval_header m"
                            + " where bgm_app_header_seq in (select distinct bgm_app_header_seq"
                                                        + " from bgm_receive r"
                                                            + " inner join bgm_approval a on r.bgm_app_seq = a.bgm_app_seq"
                                                        + " where r.bgm_rec_header_seq = " + bgm_rec_header_seq + ")";

                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ApproveTransaction(string HeaderID, string UserID)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@HeaderID", HeaderID);
                dbManager.AddParameters(1, "@usr", HeaderID);
                dbManager.Open();
                dbManager.BeginTransaction();
                string strsql = "select F_Auto_Insert_Project(@HeaderID, @usr)";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                strsql = "update bgm_receive_header set record_status = 'A' where bgm_rec_header_seq = @HeaderID";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);

                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public DataTable GetDataProjectSourceBySequence(string bgm_prj_header_seq, string bgm_prj_seq)
        {
            try
            {

                return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        public void UpdateApprovalSource(string bgm_app_source_seq, decimal approve_amount, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", bgm_app_source_seq);
                dbManager.AddParameters(1, "@approve_amount", approve_amount);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_approval_source SET approve_amount=@approve_amount, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_app_source_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }


        public void UpdateApprovalStrategy(string bgm_app_strat_seq, decimal approve_amount, string UpdateBy)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@sequence", bgm_app_strat_seq);
                dbManager.AddParameters(1, "@approve_amount", approve_amount);
                dbManager.AddParameters(2, "@updated_by", UpdateBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Now);
                string strsql = "UPDATE bgm_approval_strategy SET approve_amount=@approve_amount, updated_by=@updated_by, updated_when=@updated_when WHERE bgm_app_strat_seq=@sequence";
                dbManager.ExecuteNonQuery(CommandType.Text, strsql);
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
